using System;
using System.Drawing;
using System.Reflection;
using System.Collections;
using System.IO;
using Aspose.Excel;

namespace AsposeTools
{
	/// <summary>
	/// Summary description for AsposeTools.
	/// </summary>
	public class AsposeHelper
	{
		//The maximum length of cell comments in Aspose.Excel is 4111. We will extend it in the future release. 
		public static int MaxCommentLength = 4111;
		public static Color LightGrayColor = Color.FromArgb(192,192,192);
		public static Color OverlayColor = Color.Gray;
		
		public static Color[] Colors = new [] {Color.Blue, Color.Green, Color.Red, 
			Color.FromArgb(255,204,0),	Color.Purple, Color.Cyan,
			Color.FromArgb(204,255,204), Color.Magenta, Color.FromArgb(128,0,0), 
			Color.Yellow, Color.Black};
		
		private static readonly License excelLicence;

		static AsposeHelper()
		{
			Stream stream = Assembly.GetAssembly(typeof(WordInterop)).GetManifestResourceStream(
				"AsposeTools.Aspose.Excel.lic");
			excelLicence = new License();
			excelLicence.SetLicense(stream);
		}

		public static void FormatCell(string fontName, Cell range, short fontHeight, bool bold,
			TextAlignmentType valign, TextAlignmentType halign)
		{
			if (range != null)
			{
				range.Style.Font.IsBold = bold;
				range.Style.Font.Name = fontName;
				range.Style.Font.Size = fontHeight;									
				range.Style.HorizontalAlignment = halign;
				range.Style.VerticalAlignment = valign;
			}
		}

		public static Color GetExcelColor(Excel app, int inx)
		{
			if (inx == -1)
			{
				return app.Colors[app.Colors.Length - 1];
			}
			return GetExcelColor(inx);
		}

		public static Color GetExcelColor(int inx)
		{
			if (inx >= Colors.Length)		
			{
				inx %= Colors.Length;			
				return Colors[inx];
			}
			else
				return Colors[inx];
		}

		public static Color GetColorByIndex(int inx)
		{
			if (inx >= Colors.Length)		
			{
				inx %= Colors.Length;			
				return Colors[inx];
			}
			else if (inx == -1)
				return OverlayColor;
			else
				return Colors[inx];
		}

		public static bool IsRepeatedColor(int inx)
		{
			return (inx >= Colors.Length);
		}

		public static bool IsFGColorWhite(Color color)
		{
			int br = (color.R * (int)(0.299 * 65536) + 
				color.G * (int)(0.587 * 65536) + 
				color.B * (int)(0.114 * 65536)) >> 16;
			if (br > 128)
				return false;
			return true;		
		}

		public static Color GetTransparentFGColor(Color bgColor)
		{
			if (!IsFGColorWhite(bgColor))
				return Color.Black;
			return Color.White;			
		}

		public static string GetletterToIndex(int index)
		{
			string[] alpha = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M",
								 "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};

			if (index < 1 || index > 260)
				throw new ApplicationException("Index of the cell exceeds the maximum value");
				

			if (index <= alpha.Length)
				return alpha[index - 1];

			int N = index / alpha.Length;
			return alpha[N - 1] + alpha[index - N * alpha.Length - 1];
		}

		public static void FormatCell(string fontName, Range range, short fontHeight, bool bold,
			TextAlignmentType valign, TextAlignmentType halign)
		{
			if (range != null)
			{
				range.Style.Font.IsBold = bold;
				range.Style.Font.Name = fontName;
				range.Style.Font.Size = fontHeight;									
				range.Style.HorizontalAlignment = halign;
				range.Style.VerticalAlignment = valign;
			}
		}

		public static void FormatCell(string fontName, Worksheet sheet, int row_from, int col_from, int row_to, int col_to, 
			short fontHeight, bool bold, TextAlignmentType valign, TextAlignmentType halign)
		{

			row_from--;
			row_to--;
			col_from--;
			col_to--;
			for (int i = row_from; i <= row_to; i++)
				for (int j = col_from; j <= col_to; j++)
				{
					FormatCell(fontName, sheet.Cells[i, (byte)j], fontHeight, bold, valign, halign);
				}
		}		

		public static string GetFormatDate(DateTime current, bool withTime, string separator)
		{
			string month = current.Month.ToString();
			string day = current.Day.ToString();
			if (month.Length == 1)
				month = "0" + month;
			if (day.Length == 1)
				day = "0" + day;
			return withTime ? 
				string.Format("{0}{1}{2}{3}{4} {5}", month, separator, day, separator, current.Year, current.ToShortTimeString()) : 
				string.Format("{0}{1}{2}{3}{4}", month, separator, day, separator, current.Year);
		}

		public static void FormatCell(Cell range, short fontHeight, bool bold,
			TextAlignmentType valign, TextAlignmentType halign)
		{
			FormatCell("Tahoma", range, fontHeight, bold, valign, halign);
		}

		public static void FormatCell(Range range, short fontHeight, bool bold,
			TextAlignmentType valign, TextAlignmentType halign)
		{
			FormatCell("Tahoma", range, fontHeight, bold, valign, halign);
		}

		public static void FormatCell(Worksheet sheet, int row_from, int col_from, int row_to, int col_to,
			short fontHeight, bool bold, TextAlignmentType valign, TextAlignmentType halign)
		{
			row_from--;
			row_to--;
			col_from--;
			col_to--;

			for (int i = row_from; i <= row_to; i++)
			{
				for (int j = col_from; j <= col_to; j++)
				{
					FormatCell(sheet.Cells[i, (byte)j], fontHeight, bold, valign, halign);
				}
			}		
		}				

		public static void FillCellColor(Cell range, Color color)
		{
			if (range != null)
			{
				range.Style.ForegroundColor = color;
			}
		}

		public static void FillCellHatch(Cell range)
		{
			if (range != null)
			{
				range.Style.BackgroundColor = Color.White;
				range.Style.ForegroundColor = Color.Gray;
				range.Style.Pattern = BackgroundType.ThinDiagonalStripe; 
			}
		}

		public static void FillCellColor(Range range, Color color)
		{
			if (range != null)
			{
				if (range.Style != null)
				{
					range.Style.ForegroundColor = color;
				}
				DrawBorders(range, true);
			}
		}

		public static void FillCellColor(Worksheet sheet, int row_from, int col_from, int row_to, 
			int col_to, Color color)
		{
			row_from--;
			row_to--;
			col_from--;
			col_to--;

			for (int i = row_from; i <= row_to; i++)
			{
				for (int j = col_from; j <= col_to; j++)
				{
					FillCellColor(sheet.Cells[i, (byte)j], color);
				}
			}		
		}


		public static void DrawBorders(Worksheet sheet, int row_from, int col_from, int row_to, 
			int col_to, bool bWhite)
		{
			row_from--;
			row_to--;
			col_from--;
			col_to--;

			for (int i = row_from; i <= row_to; i++)
			{
				if (sheet.Cells[i, (byte)col_from].Style.Borders[BorderType.LeftBorder].LineStyle != CellBorderType.Thick)
				{
					sheet.Cells[i, (byte)col_from].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
				}
				if (sheet.Cells[i, (byte)col_to].Style.Borders[BorderType.RightBorder].LineStyle != CellBorderType.Thick)
				{
					sheet.Cells[i, (byte)col_to].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
				}
			}		
			for (int i = col_from; i <= col_to; i++)
			{
				if (sheet.Cells[row_from, (byte)i].Style.Borders[BorderType.TopBorder].LineStyle != CellBorderType.Thick)
				{
					sheet.Cells[row_from, (byte)i].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
				}
				if (sheet.Cells[row_to, (byte)i].Style.Borders[BorderType.BottomBorder].LineStyle != CellBorderType.Thick)
				{
					sheet.Cells[row_to, (byte)i].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
				}
			}		
		}

		public static void DrawBorders(Cell range, bool bWhite)
		{
			if (range != null)
			{
				ArrayList brs = new ArrayList
				{
					range.Style.Borders[BorderType.BottomBorder], 
					range.Style.Borders[BorderType.TopBorder],
					range.Style.Borders[BorderType.LeftBorder], 
					range.Style.Borders[BorderType.RightBorder]
				};
				foreach(Border b in brs)
				{
					if (b.LineStyle != CellBorderType.Thick)
					{
						b.LineStyle = CellBorderType.Thin;
					}
					b.Color = bWhite ? Color.White : Color.Black;
				}
			}
		}

		public static void DrawBorders(Range range, bool bWhite)
		{
			if (range != null)
			{
				ArrayList brs = new ArrayList
				{
					range.Style.Borders[BorderType.BottomBorder], 
					range.Style.Borders[BorderType.TopBorder],
					range.Style.Borders[BorderType.LeftBorder], 
					range.Style.Borders[BorderType.RightBorder]
				};
				foreach(Border b in brs)
				{
					b.LineStyle = CellBorderType.Thin;
					b.Color = bWhite ? Color.White : Color.Black;
				}
			}
		}

		private static void DrawBoldBorders(Style style, bool left, bool rigth, bool top, bool bottom)
		{
			if (style != null)
			{
				ArrayList brs = new ArrayList();
				if (bottom)
				{
					brs.Add(style.Borders[BorderType.BottomBorder]);
				}
				if (top)
				{
					brs.Add(style.Borders[BorderType.TopBorder]);
				}
				if (left)
				{
					brs.Add(style.Borders[BorderType.LeftBorder]);
				}
				if (rigth)
				{
					brs.Add(style.Borders[BorderType.RightBorder]);
				}

				foreach(Border b in brs)
				{
					b.LineStyle = CellBorderType.Thick;
					b.Color = Color.Black;
				}
			}
		}

		public static void DrawBoldBorders(Cell cell, bool left, bool rigth, bool top, bool bottom)
		{
			if (cell != null)
			{
				DrawBoldBorders(cell.Style, left, rigth, top, bottom);
			}
		}

		public static void DrawMediumBorders(Cell cell, bool left, bool rigth, bool top, bool bottom)
		{
			if (cell != null)
			{
				if (cell.Style != null)
				{
					ArrayList brs = new ArrayList();
					if (bottom)
					{
						brs.Add(cell.Style.Borders[BorderType.BottomBorder]);
					}
					if (top)
					{
						brs.Add(cell.Style.Borders[BorderType.TopBorder]);
					}
					if (left)
					{
						brs.Add(cell.Style.Borders[BorderType.LeftBorder]);
					}
					if (rigth)
					{
						brs.Add(cell.Style.Borders[BorderType.RightBorder]);
					}

					foreach(Border b in brs)
					{
						b.LineStyle = CellBorderType.Medium;
						b.Color = Color.Black;
					}
				}			
			}
		}

		public static void DrawBoldBorders(Range range, bool left, bool rigth, bool top, bool bottom)
		{
			if (range != null)
			{
				DrawBoldBorders(range.Style, left, rigth, top, bottom);
			}
		}


		public static void DrawAllBorders(Worksheet sheet, int row_from, int col_from, int row_to, 
			int col_to, bool bWhite)
		{
			row_from--;
			row_to--;
			col_from--;
			col_to--;
			Color color = (bWhite) ? Color.White : Color.Black;

			for (int i = row_from; i <= row_to; i++)
			{
				for (byte j = (byte)col_from; j <= (byte)col_to; j++)
				{
					if (sheet.Cells[i, j].Style.Borders[BorderType.LeftBorder].LineStyle == CellBorderType.None)
					{
						sheet.Cells[i, j].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
						sheet.Cells[i, j].Style.Borders[BorderType.LeftBorder].Color = color;
					}
					if (sheet.Cells[i, j].Style.Borders[BorderType.RightBorder].LineStyle == CellBorderType.None)
					{
						sheet.Cells[i, j].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
						sheet.Cells[i, j].Style.Borders[BorderType.RightBorder].Color = color;
					}
					if (sheet.Cells[i, j].Style.Borders[BorderType.TopBorder].LineStyle == CellBorderType.None)
					{
						sheet.Cells[i, j].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
						sheet.Cells[i, j].Style.Borders[BorderType.TopBorder].Color = color;
					}
					if (sheet.Cells[i, j].Style.Borders[BorderType.BottomBorder].LineStyle == CellBorderType.None)
					{
						sheet.Cells[i, j].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
						sheet.Cells[i, j].Style.Borders[BorderType.BottomBorder].Color = color;
					}
				}
			}		
		}

		public static Worksheet StartExcel(out Excel excel)
		{
			excel = new Excel();
			return excel.Worksheets[0];
		}

		public static Cell GetRange(Worksheet wks, int row, int col)
		{
			row--;
			col--;
			return wks.Cells[row, (byte)col];
		}

		public static Range GetRange(Worksheet sheet, int row1, int col1, int row2, int col2)
		{
			row1--;
			col1--;
			row2--;
			col2--;
			Range r = sheet.Cells.CreateRange(row1, (byte)col1, row2 - row1 + 1, col2 - col1 + 1);		
			return r;
		}

		public static void AutoFitColumns(Worksheet sheet, int from, int to)
		{
			from--;
			to--;
			for (int i = from; i <= to; i++)
			{
				sheet.AutoFitColumn((byte)i);
			}		
		}

		public static void AutoFitRows(Worksheet sheet, int from, int to)
		{
			from--;
			to--;
			for (int i = from; i <= to; i++)
			{
				sheet.AutoFitRow(i);
			}
		}

		public static void SetRowHeight(Worksheet sheet, int row, double height)
		{
			// NOTE: that strange but we should pass row index to inplace of column index
			// to achieve correct result.
			Range r = sheet.Cells.CreateRange(row - 1, 0, 1, 1);
			r.RowHeight = height;
		}

		public static bool SheetExist(Excel app, string sheetName)
		{
			foreach (Worksheet wks in app.Worksheets)
			{
				if (wks.Name == sheetName)
				{
					return true;		
				}
			}
			return false;
		}

		public static void AddComment(Worksheet sheet, int row, byte col, string comment)
		{
			AddComment(sheet, row, col, comment, -1, -1);
		}

		public static void AddComment(Worksheet sheet, int row, byte col, string comment, int width, int height)
		{
			//width and height is measured in number of columns/rows
			int comInx = sheet.Comments.Add(row, col);
			Comment commentObj = sheet.Comments[comInx];

			commentObj.Note = comment.Length > MaxCommentLength ? 
				comment.Substring(0, MaxCommentLength) : comment;

			if (width != -1)
			{
				commentObj.Width = width;
			}

			if (height != -1)
			{
				commentObj.Height = height;
			}
			
		}

		public static string GetExcelFormatString(string formatString)
		{
			//string tmp = formatString;
			if (formatString != null)
			{
				if (formatString.Equals("P"))
				{
					formatString = "#,##0.##%";
				}
				return formatString.Replace(".##", ".00");
			}
			else
			{
				return null;
			}
		}
	}

	public class ExcelColorsMapper
	{
		private readonly Hashtable colorsMapping = new Hashtable();
		private readonly Excel app;
		private int minColorIndex = 16;
		
		public ExcelColorsMapper(Excel app)
		{
			this.app = app;
		}

		public Color GetMappedColor(Color sourceColor)
		{
			if (sourceColor == Color.Empty)
			{
				return sourceColor;
			}

			int colorIndex;
			if (!colorsMapping.ContainsKey(sourceColor))
			{
				int newIndex = app.Colors.Length - colorsMapping.Count - 1;
				if (newIndex < minColorIndex)
					throw new Exception("Maximum limit of custom colors exceeded");
				// not working !
				app.ChangePalette(sourceColor, newIndex);
				//
				app.Colors[newIndex] = sourceColor;
				colorsMapping[sourceColor] = newIndex;
				colorIndex = newIndex;
			}
			else
			{
				colorIndex = (int)colorsMapping[sourceColor];
			}
			return app.Colors[colorIndex];
		}

	}

	public class ExcelHelper
	{
		private const FileFormatType excelFormatFile = FileFormatType.Excel2000;

		public static void Save(Excel excel, string fileName)
		{
			excel.Save(fileName, excelFormatFile);		
		}
	}
}
